package com.yc.api.d1030;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Scanner;

public class Class_homework {
	
	private static String driver;
	
	private static String url;
	
	private static String user;
	
	private static String password;
	
	private static Scanner sc=new Scanner(System.in);
	
	
	public static void main(String[] args) {
		// 实现课表数据的增删改查
		while(true) {
			System.out.println("***************************");
			System.out.println("***    1.查询所有课表    ***");
			System.out.println("***    2.查询周次课表    ***");
			System.out.println("***      3.增加课程      ***");
			System.out.println("***      4.删减课程      ***");
			System.out.println("***      5.修改课程      ***");
			System.out.println("***        6.退出        ***");
			System.out.println("***************************");
			System.out.println("请输入你的选择");
			
			String choice=sc.nextLine();
			switch(choice) {
			case "1":
				selectAllClasses("select * from class_s order by week,weekday");
				break;
			case "2":
				selectWeekClasses("select * from class_s where week=?");
				break;
			case "3":
				//?:第几周
				//周几
				//
				save("insert into class_s values(?,?,?,?,?,?,?)");
				break;
			case "4":
				delete("delete class_s where week=? and weekday=?");
				break;
			case "5":
				update("update class_s set ");
				break;
			case "6":
				System.exit(0);
			}
		}
	}
	
	
	static {
		try {
			Properties prop=new Properties();
			//获得类加载器
			ClassLoader loader=Class_homework.class.getClassLoader();
			InputStream in=loader.getResourceAsStream("jdbc.properties");
			//判断是否读取失败
			if(in==null) {
				throw new Exception("配置文件不存在");
			}
			
			prop.load(in);
			driver=prop.getProperty("jdbc.driver");
			url=prop.getProperty("jdbc.url");
			user=prop.getProperty("jdbc.username");
			password=prop.getProperty("jdbc.password");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	
	//获得连接
	public static Connection getConnection() {
		Connection conn=null;
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,user,password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return conn;
	}
	
	
	//查询所有课表（格式是周次，课程名称（一共五节，上午下午各两节，晚上一节））
	public static void selectAllClasses(String sql){
		try(Connection conn=getConnection()){
			PreparedStatement ps=conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			System.out.println(rs);
			System.out.printf("%-10s %-10s %-10s %-10s %-10s %-10s %-10s\n","周次","周几","第一节课","第二节课","第三节课","第四节课","第五节课");
			while(rs.next()) {
				System.out.printf("%-10d %-10d %-10s %-10s %-10s %-10s %-15s\n",
						rs.getInt("week"),rs.getInt("weekday"),rs.getString("class_1"),
						rs.getString("class_2"),rs.getString("class_3"),rs.getString("class_4"),rs.getString("class_5"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	//按周次查询课表
	public static List<Map<String,Object>> selectWeekClasses(String sql){
		try(Connection conn=getConnection()){
			PreparedStatement ps=conn.prepareStatement(sql);
			
			System.out.println("请输入你想查询的周次（周几）");
			String _week=sc.nextLine();
			int week=Integer.valueOf(_week);
			ps.setInt(1, week);
			
			ResultSet rs=ps.executeQuery();
			
			System.out.printf("%-10s %-10s %-10s %-10s %-10s %-10s %-10s\n","周次","周几","第一节课","第二节课","第三节课","第四节课","第五节课");
			while(rs.next()) {
				System.out.printf("%-10d %-10d %-10s %-10s %-10s %-10s %-15s\n",
						rs.getInt("week"),rs.getInt("weekday"),rs.getString("class_1"),
						rs.getString("class_2"),rs.getString("class_3"),rs.getString("class_4"),rs.getString("class_5"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	
	//对课表进行增删改查之类的操作
	//增  insert into class_s values(?,?,?,?,?,?,?)
	public static void save(String sql) {
		try(Connection conn=getConnection()){
			PreparedStatement ps=conn.prepareStatement(sql);
			System.out.println("请输入你想修改的课表的信息，哪一周？");
			String _week=sc.nextLine();
			int week=Integer.valueOf(_week);
			
			System.out.println("周几？");
			String _weekday=sc.nextLine();
			int weekday=Integer.valueOf(_weekday);
			
			System.out.println("第一节课什么课？（没有的填无）");
			String class_1=sc.nextLine();
			
			System.out.println("第二节课什么课？（没有的填无）");
			String class_2=sc.nextLine();
			
			System.out.println("第三节课什么课？（没有的填无）");
			String class_3=sc.nextLine();
			
			System.out.println("第四节课什么课？（没有的填无）");
			String class_4=sc.nextLine();
			
			System.out.println("第五节课什么课？（没有的填无）");
			String class_5=sc.nextLine();
			
			ps.setInt(1, week);
			ps.setInt(2, weekday);
			ps.setString(3, class_1);
			ps.setString(4, class_2);
			ps.setString(5, class_3);
			ps.setString(6, class_4);
			ps.setString(7, class_5);
			
			int result=ps.executeUpdate();
			
			 if(result!=0) { 
				 System.out.println("添加数据成功!");
			 }else {
			 System.out.println("添加数据失败!"); }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	//删  delete table class_s where week=? and weekday=?
	public static void delete(String sql) {
		try(Connection conn=getConnection()){
			PreparedStatement ps=conn.prepareStatement(sql);
			System.out.println("请输入你想删除的课程表的周次");
			String _week=sc.nextLine();
			int week=Integer.valueOf(_week);
			
			System.out.println("请输入你想删除的课程表中的周几");
			String _weekday=sc.nextLine();
			int weekday=Integer.valueOf(_weekday);
			
			ps.setInt(1, week);
			ps.setInt(2, weekday);
			System.out.println("请确认你是否要删除该条课程表信息 yes/no");
			String choice=sc.nextLine();
			if("yes".equals(choice)) {
				int result=ps.executeUpdate();
				if(result!=0) {
					System.out.println("删除数据成功!");
				}else {
					System.out.println("删除数据失败");
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	//改  update class_s set 
	public static void update(String sql) {
		try(Connection conn=getConnection()){
			String choice=null;
			String choose="";
			
			System.out.println("请输入你想修改的课程表的周次");
			String _week=sc.nextLine();
			int week=Integer.valueOf(_week);
			
			System.out.println("请输入你想修改的课程表的周几");
			String _weekday=sc.nextLine();
			int weekday=Integer.valueOf(_weekday);
			
			System.out.printf("你是否想要修改的第%d,星期%d的第一节课yes/no\n",week,weekday);
			choice=sc.nextLine();
			String class_1=null;
			if("yes".equals(choice)) {
				System.out.printf("你修改的第%d,星期%d的第一节课为\n",week,weekday);
				class_1=sc.nextLine();
				choose+="1";
			}
			
			System.out.printf("你是否想要修改的第%d,星期%d的第二节课yes/no\n",week,weekday);
			choice=sc.nextLine();
			String class_2=null;
			if("yes".equals(choice)) {
				System.out.printf("你修改的第%d,星期%d的第二节课为\n",week,weekday);
				class_2=sc.nextLine();
				choose+="2";
			}
			
			System.out.printf("你是否想要修改的第%d,星期%d的第三节课yes/no\n",week,weekday);
			choice=sc.nextLine();
			String class_3=null;
			if("yes".equals(choice)) {
				System.out.printf("你修改的第%d,星期%d的第三节课为\n",week,weekday);
				class_3=sc.nextLine();
				choose+="3";
			}
			
			System.out.printf("你是否想要修改的第%d,星期%d的第四节课 yes/no\n",week,weekday);
			choice=sc.nextLine();
			String class_4=null;
			if("yes".equals(choice)) {
				System.out.printf("你修改的第%d,星期%d的第四节课为\n",week,weekday);
				class_4=sc.nextLine();
				choose+="4";
			}
			
			System.out.printf("你是否想要修改的第%d,星期%d的第五节课yes/no\n",week,weekday);
			choice=sc.nextLine();
			String class_5=null;
			if("yes".equals(choice)) {
				System.out.printf("你修改的第%d,星期%d的第五节课为\n",week,weekday);
				class_5=sc.nextLine();
				choose+="5";
			}
			
			for(char c: choose.toCharArray()) {
				sql+=("class_"+c+"= ? , ");
			}
			
			sql=sql.substring(0,sql.length()-2);
			
			sql+=" where week=" + week + " and weekday=" + weekday;
			//System.out.println(sql);  12
			PreparedStatement ps=conn.prepareStatement(sql);
			
			int j=1;
			for(int c: choose.toCharArray()) {
				switch(c) {
				case '1':
					ps.setString(j, class_1);
					break;
				case '2':
					ps.setString(j, class_2);
					break;
				case '3':
					ps.setString(j, class_3);
					break;
				case '4':
					ps.setString(j, class_4);
					break;
				case '5':
					ps.setString(j, class_5);
					break;
				}
				j++;
			}
			
			int rs=ps.executeUpdate();
			
			if(rs>0) {
				System.out.println("修改数据成功");
			}else {
				System.out.println("修改数据失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
